package com.lzy.liujing.restaurant.dao;


import com.lzy.liujing.restaurant.entity.CountBy;
import com.lzy.liujing.restaurant.entity.WeekBy;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.sql.Date;
import java.util.List;

/**
 * 数据图表分析持久层
 */
@Mapper
public interface DataAnalysisDao {

    // 查询近几天的菜系销量数据

    @Select(
            " select category_id, IFNULL(SUM(count),0) as 'count' " +
            " from r_order_detail " +
            " where DATE_SUB(CURDATE(), INTERVAL #{day} DAY) <= DATE(createTime) " + // 注意修改为DAY
            " group by category_id ")
    List<CountBy> findGoodsCategorySalesByDays(@Param("day") String day);

    @Select(" select createTime, IFNULL(SUM(price * count - cost * count),0) as 'price' " +
            " from r_order_detail " +
            " where DATE_SUB(CURDATE(), INTERVAL #{day} DAY) <= DATE(createTime) " + // 注意修改为DAY
            " group by createTime ")
    List<CountBy> findDailSalesByDays(String day);

    @Select(" SELECT WEEK(createTime) AS week_number,"+
            " SUM(price * count - cost * count) AS 'price' "+
            " FROM r_order_detail"+
            "  WHERE createTime >= DATE_SUB(CURDATE(), INTERVAL #{day} MONTH) "+
            "  AND createTime < CURDATE() " +
            " GROUP BY week_number")
    List<CountBy> findWeeklyProfitForMonth(String day);
}
